const user_in = "select count(*) as count from smart_user where openid = ?";

const select_openid = "select id,role from smart_user where openid = ?";

const insert_user = "insert into smart_user(openid,nickName,avatarUrl,country,province,city,role) values (?,?,?,?,?,?,0)";

const insert_login_info = "insert into smart_login_log(login_ip,login_time) values (?,?)";

const update_profile = "update smart_user set nickName=?,avatarUrl=?,country=?,province=?,city=? where openid = ?";

const getUser_info = "select nickName,avatarUrl from smart_user where openid = ?";

const getUser_info_id = "select nickName,avatarUrl from smart_user where id = ?";

const insert_owner = "insert into smart_household_owner(user_id,house_id,owner_name,owner_tel,owner_email,owner_auth,owner_status,owner_date) values (?,?,?,?,?,?,0,?)";

const select_ownerByuserId = "select smart_household_owner.id,smart_household_owner.owner_name as household_owner,smart_building.building_name,smart_house.house_plate,smart_household_owner.owner_tel,smart_household_owner.owner_email,smart_household_owner.owner_auth,smart_household_owner.owner_status,smart_household_owner.owner_date from smart_household_owner left join smart_house on smart_household_owner.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id where smart_household_owner.user_id = ?";

//管理端
//获取业主申请列表
const select_owner_admin = "select smart_household_owner.id,smart_user.nickName,smart_building.building_name,smart_house.house_plate,smart_household_owner.owner_name,smart_household_owner.owner_tel,smart_household_owner.owner_tel,smart_household_owner.owner_email,smart_household_owner.owner_auth,smart_household_owner.owner_status,smart_household_owner.owner_date from smart_household_owner left join smart_user on smart_household_owner.user_id=smart_user.id left join smart_house on smart_household_owner.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id limit ? offset ?";
//模糊搜索关键字（nickName）
const select_owner_admin_keyWord = "select smart_household_owner.id,smart_user.nickName,smart_building.building_name,smart_house.house_plate,smart_household_owner.owner_name,smart_household_owner.owner_tel,smart_household_owner.owner_tel,smart_household_owner.owner_email,smart_household_owner.owner_auth,smart_household_owner.owner_status,smart_household_owner.owner_date from smart_household_owner left join smart_user on smart_household_owner.user_id=smart_user.id left join smart_house on smart_household_owner.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id where smart_household_owner.owner_name like concat('%',?,'%')";
//修改业主申请状态
const change_owner_admin = "update smart_household_owner set owner_status=? where id=?";
//获取申请者的openId
const select_ownerID_admin = "select user_id,openid from smart_household_owner left join smart_user on smart_household_owner.user_id = smart_user.id where smart_household_owner.id=?";
//删除访客申请
const delete_owner_admin = "delete from smart_household_owner where id = ?";

//获取系统授权用户
const select_user_admin = "select * from smart_user limit ? offset ?";
//搜索系统授权用户(nickName)
const select_user_admin_keyWord = "select * from smart_user where nickName like concat('%',?,'%')";
//系统用户角色筛选
const select_user_admin_role = "select * from smart_user where role=?";
//修改用户信息
const update_user_admin_info = "update smart_user set nickName=?,country=?,province=?,city=?,role=? where id = ?";
//删除用户
const delete_user_admin = "delete from smart_user where id = ?";
module.exports = {
    user_in,
    select_openid,
    insert_user,
    insert_login_info,
    update_profile,
    getUser_info,
    getUser_info_id,
    insert_owner,
    select_ownerByuserId,
    select_owner_admin,
    select_owner_admin_keyWord,
    change_owner_admin,
    select_ownerID_admin,
    delete_owner_admin,
    select_user_admin,
    select_user_admin_keyWord,
    select_user_admin_role,
    update_user_admin_info,
    delete_user_admin
}